
/*
 * GET home page.
 */
var crypto=require('crypto');
var fs = require('fs');
var User=require('../models/user');         //前台用户
var Page=require('../models/page');
var nodemailer = require("nodemailer");
var dateFormat = require('dateformat');
var async=require('async');
var sqlConn=require('../sqlserverConn');
 module.exports = function(app) {

     app.get('/', function (req, res) {
         async.waterfall([

             //加载培训动态数据
             function(callback){
                 var sqlstr='SELECT * from  UploadFile up where up.UploadFile_WebColumnID=1 and up.UploadFile_Status=1 order by up.UploadFile_DateTime desc'
                 sqlConn(sqlstr,function(err,dataspxdt){
                     if(dataspxdt.length>4){
                         dataspxdt.length=4;
                     }
                     callback(err,dataspxdt)
                 });

             },
             //加载培训资讯数据
             function(dataspxdt,callback){
                 var sqlstr='SELECT * from  UploadFile up where up.UploadFile_WebColumnID=10 and up.UploadFile_Status=1 order by up.UploadFile_DateTime desc'
                 sqlConn(sqlstr,function(err,dataspxzx){
                     if(dataspxzx.length>9){
                         dataspxzx.length=9;
                     }
                     callback(err,dataspxdt,dataspxzx)
                 });


             },
             //加载政策文件信息数据
             function(dataspxdt,dataspxzx,callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='SELECT * from  UploadFile up where up.UploadFile_WebColumnID=11 and up.UploadFile_Status=1 order by up.UploadFile_DateTime desc'
                 sqlConn(sqlstr,function(err,dataszcwj){
                     if(dataszcwj.length>9){
                         dataszcwj.length=9;
                     }
                     callback(err,dataspxdt,dataspxzx,dataszcwj)
                 });


             },
             //加载在线学习信息数据
             function(dataspxdt,dataspxzx,dataszcwj,callback){
                 var now =new Date();
                 console.log(dateFormat(now,'yyyy/mm/dd'));

                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr="select * from TrainingCourseware left join Courseware on TrainingCourseware_CoursewareID=Courseware_ID left join Training on TrainingCourseware_TrainingID=Training_ID left join TrainingSort  on Training_SortID=TrainingSort_ID left join TrainingOrganization  on Training_OrganizationID=TrainingOrganization_ID where TrainingCourseware_Status=1 and Training_Status=1 and Training_EndDate>'"+dateFormat(now,'yyyy/mm/dd')+"' order by TrainingCourseware_ID desc"
                 sqlConn(sqlstr,function(err,dataszxxx){
                     if(dataszxxx.length>4){
                         dataszxxx.length=4;
                     }
                     callback(err,dataspxdt,dataspxzx,dataszcwj,dataszxxx)
                 });


             },
             //人才风采
             function(dataspxdt,dataspxzx,dataszcwj,dataszxxx,callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select * from PersonPre where PersonPre_Status=1'
                 sqlConn(sqlstr,function(err,datasrwfc){
                     if(datasrwfc.length>5){
                         datasrwfc.length=5;
                     }
                     callback(err,dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc)
                 });


             },
             //加载单位链接信息数据
             function(dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select * from DepartmentLink dl left join Department dp on dl.DepartmentLink_DepartmentID=dp.Department_ID where dl.DepartmentLink_Status=1 and dp.Department_ParentID=1'
                 sqlConn(sqlstr,function(err,datasdwlj){
                     callback(err,dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj)
                 });


             },
             //加载软件下载信息数据
             function(dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj,callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select * from Software sw where sw.Software_Status=1 order by sw.Software_ID desc'
                 sqlConn(sqlstr,function(err,datasrjxz){
                     callback(err,dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj,datasrjxz)
                 });


             },

             //加载图片新闻---该查询加载速度很慢
             function(dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj,datasrjxz,callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select * from PicNews pn left join FileContent fc on pn.PicNews_PicContentID=fc.FileContent_ID where pn.PicNews_Status=1 order by PicNews_ID desc'
                 sqlConn(sqlstr,function(err,datastpxx){
                     if(datastpxx.length>5){
                         datastpxx.length=5;
                     }
                     callback(err,dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj,datasrjxz,datastpxx)
                 });


             }
         ], function (err,dataspxdt,dataspxzx,dataszcwj,dataszxxx,datasrwfc,datasdwlj,datasrjxz,datastpxx) {
//             console.log(dataszxxx)
             res.render('index', {
                 title: '首页',
                 dataspxdt:dataspxdt,
                 dataspxzx:dataspxzx,
                 dataszcwj:dataszcwj,
                 dataszxxx:dataszxxx,
                 datasrwfc:datasrwfc,
                 datasdwlj:datasdwlj,
                 datasrjxz:datasrjxz,
                 datastpxx:datastpxx,
                 menu: 'menu1'
             });
         });
     });
     //加载培训资讯、成果展示、政策文件的ajax
     app.get('/getDatas', function (req, res) {

         var sqlstr='SELECT * from  UploadFile up where up.UploadFile_WebColumnID='+req.query.id+'and up.UploadFile_Status=1 order by up.UploadFile_DateTime desc'
         sqlConn(sqlstr,function(err,datas){
             if(datas.length>9){
                 datas.length=9;
             }
             res.render('getDatas', {
                 datas:datas,
                 id:req.query.id
             });
         });
     });
     //加载课件信息的ajax
     app.get('/getkejianDatas', function (req, res) {
         var sqlstr='select * from Courseware cs where cs.Courseware_SortID='+req.query.id+'and Courseware_Status=1 and Courseware_Public=1 order by cs.Courseware_ID desc'
         sqlConn(sqlstr,function(err,datas){

             if(datas.length>10){
                 datas.length=10;
             }
             res.render('getkejianDatas', {
                 datas:datas
             });
         });
     });
     //加载供求信息的ajax
     app.get('/getgqxxDatas', function (req, res) {
         var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'

         sqlConn(sqlstr,function(err,datas){
             if(datas.length>9){
                 datas.length=9;
             }
             res.render('getgqxxDatas', {

                 datas:datas
             });
         });
     });
     //加载在线学习的ajax
     app.get('/getzxxxDatas', function (req, res) {
         var now =new Date();
         var sqlstr="select * from TrainingCourseware left join Courseware on TrainingCourseware_CoursewareID=Courseware_ID left join Training on TrainingCourseware_TrainingID=Training_ID left join TrainingSort  on Training_SortID=TrainingSort_ID left join TrainingOrganization  on Training_OrganizationID=TrainingOrganization_ID where TrainingCourseware_Status=1 and Training_Status=1 and Training_EndDate>'"+dateFormat(now,'yyyy/mm/dd')+"' order by TrainingCourseware_ID desc"
         sqlConn(sqlstr,function(err,datas){
             if(datas.length>4){
                 datas.length=4;
             }
             res.render('getzxxxDatas', {
                 datas:datas
             });
         });


     });
     //加载公司新闻的ajax
     app.get('/getgsxwDatas', function (req, res) {

         var sqlstr='select dp.Department_ID from Department dp where dp.Department_ParentID='+req.query.id
         sqlConn(sqlstr,function(err,datas){
             var str='('+req.query.id
             if(datas.length!=0){
                 str+=','
             }
             datas.forEach(function(data,index){

                 if(index+1!=datas.length){
                     str+=data.Department_ID+','
                 }else{
                     str+=data.Department_ID
                 }

             })
             str+=')'

             sqlstr='SELECT * from UploadFile uf left join Department dt on uf.UploadFile_DepartmentID=dt.Department_ID where uf.UploadFile_DepartmentID in'+str+' and uf.UploadFile_Status=1 order by uf.UploadFile_ID desc'
             sqlConn(sqlstr,function(err,results){
                 res.render('getgsxwDatas', {
                     datas:results
                 });
             })
         });


     });
     app.get('/showupload', function (req, res) {
         var title;
         var menu;
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 switch (req.query.id){
                     case '1':
                     case '2':
                     case '14':menu='menu2';break;
                     case '10':menu='menu3';break;
                     case '11':
                     case '12':
                     case '13':menu='menu6';break;
                 }
                 if(req.query.id==1||req.query.id==11){


                     sqlConn('select * from WebColumn wc where wc.WebColumn_ID='+req.query.id,function(err,data){
                         title=data[0].WebColumn_Name;
                         var sqlstr='select wc.WebColumn_ID from WebColumn wc where wc.WebColumn_Status=1 and wc.WebColumn_ParentID='+req.query.id
                         sqlConn(sqlstr,function(err,datas){
                             var str='('+req.query.id
                             if(datas.length!=0){
                                 str+=','
                             }
                             datas.forEach(function(data,index){

                                 if(index+1!=datas.length){
                                     str+=data.WebColumn_ID+','
                                 }else{
                                     str+=data.WebColumn_ID
                                 }

                             })
                             str+=')'
                             sqlstr='select count(1) count from UploadFile uf where uf.UploadFile_WebColumnID in '+str +'and uf.UploadFile_Status=1'
                             sqlConn(sqlstr,function(err,count){
                                 if(count.length!=0){
                                     page.totalPage=Math.ceil(count[0].count/10);
                                     sqlstr='SELECT TOP 10 *'
                                     +'FROM'
                                     +'('
                                     +'    SELECT ROW_NUMBER() OVER (ORDER BY UploadFile_ID desc) AS RowNumber,* FROM UploadFile where UploadFile_Status=1 and UploadFile_WebColumnID in'+str
                                     +' ) A'
                                     +' WHERE RowNumber>10*'+(page.curPage-1)
                                     sqlConn(sqlstr,function(err,results){
                                         callback(err,results)
                                     })
                                 }else{
                                     var results=[];
                                     callback(err,results)

                                 }

                             });
                         });
                     })

                 }else{
                     sqlConn('select * from WebColumn wc where wc.WebColumn_ID='+req.query.id,function(err,data){
                         title=data[0].WebColumn_Name;
                         sqlstr='select count(1) count from UploadFile uf where uf.UploadFile_WebColumnID='+req.query.id +'and uf.UploadFile_Status=1'
                         sqlConn(sqlstr,function(err,count){
                             console.log(count)
                             if(count.length!=0){
                                 page.totalPage=Math.ceil(count[0].count/10);
                                 sqlstr='SELECT TOP 10 *'
                                     +'FROM'
                                     +'('
                                     +'    SELECT ROW_NUMBER() OVER (ORDER BY UploadFile_ID desc) AS RowNumber,* FROM UploadFile where UploadFile_Status=1 and UploadFile_WebColumnID='+req.query.id
                                     +') A'
                                     +' WHERE RowNumber>10*'+(page.curPage-1)
                                 sqlConn(sqlstr,function(err,results){
                                     callback(err,results)
                                 })
                             }else{
                                 var results=[];
                                 callback(err,results)
                             }


                         });
                     })

                 }

             }

         ], function (err,results) {
             res.render('showupload', {
                 title: '培训动态',
                 results:results,
                 title:title,
                 page:page,
                 menu: menu
             });
         });

     });
     app.get('/search', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 var reg=/[ ]/g;
                 sqlstr="select count(1) count from UploadFile uf where uf.UploadFile_Status=1 and uf.UploadFile_FileName like '%"+req.query.keyword.replace(reg,'')+"%'"
                 sqlConn(sqlstr,function(err,count){
                     page.totalPage=Math.ceil(count[0].count/10);
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +"    SELECT ROW_NUMBER() OVER (ORDER BY UploadFile_ID desc) AS RowNumber,* FROM UploadFile where UploadFile_Status=1 and UploadFile_FileName like '%"+req.query.keyword.replace(reg,'')+"%'"
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,results){
                         callback(err,results)
                     })

                 });
             }


         ], function (err,results) {
             res.render('search', {
                 title: '培训动态',
                 results:results,
                 page:page,
                 menu: "menu1"
             });
         });

     });
     app.get('/onlineLearn', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 var now =new Date();
                 console.log(dateFormat(now,'yyyy/mm/dd'));

                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr="select count(1) count from TrainingCourseware left join Courseware on TrainingCourseware_CoursewareID=Courseware_ID left join Training on TrainingCourseware_TrainingID=Training_ID left join TrainingSort  on Training_SortID=TrainingSort_ID left join TrainingOrganization  on Training_OrganizationID=TrainingOrganization_ID where TrainingCourseware_Status=1 and Training_Status=1 and Training_EndDate>'"+dateFormat(now,'yyyy/mm/dd')+"'"

                 sqlConn(sqlstr,function(err,count){

                     page.totalPage=Math.ceil(count[0].count/10)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +"    SELECT ROW_NUMBER() OVER (ORDER BY TrainingCourseware_ID desc) AS RowNumber,* FROM TrainingCourseware left join Courseware on TrainingCourseware_CoursewareID=Courseware_ID left join Training on TrainingCourseware_TrainingID=Training_ID left join TrainingSort  on Training_SortID=TrainingSort_ID left join TrainingOrganization  on Training_OrganizationID=TrainingOrganization_ID where TrainingCourseware_Status=1 and Training_Status=1 and Training_EndDate>'"+dateFormat(now,'yyyy/mm/dd')+"'"
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,dataszxxx){
                         callback(err,dataszxxx)
                     })

                 });


             }
         ], function (err,dataszxxx) {
             res.render('onlineLearn', {
                 title: '在线学习',
                 dataszxxx:dataszxxx,
                 page:page,
                 menu: "menu4"
             });
         });

     });
     app.get('/courseware', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select count(1) count from Courseware cw left join CoursewareSort cs on cw.Courseware_SortID=cs.CoursewareSort_ID where cw.Courseware_Status=1 and cs.CoursewareSort_Status=1'

                 sqlConn(sqlstr,function(err,count){

                     page.totalPage=Math.ceil(count[0].count/10)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +'    SELECT ROW_NUMBER() OVER (ORDER BY Courseware_ID desc) AS RowNumber,* FROM Courseware left join CoursewareSort on Courseware_SortID=CoursewareSort_ID where Courseware_Status=1 and CoursewareSort_Status=1'
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,results){
                         callback(err,results)
                     })

                 });


             }

         ], function (err,results) {
             res.render('courseware', {
                 title: '课件超市',
                 results:results,
                 page:page,
                 menu: "menu7"
             });
         });

     });
     app.get('/message', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var reg=/[ ]/g;
                 var sqlstr='select count(1) count from Message ms where ms.Message_Status=1'
                 if(req.query.name){
                     sqlstr+=" and ms.Message_PersonName like '%"+req.query.name.replace(reg,'')+"%'"
                 }
                 if(req.query.content){
                     sqlstr+=" and ms.Message_Content like '%"+req.query.content.replace(reg,'')+"%'"
                 }

                 sqlConn(sqlstr,function(err,count){

                     page.totalPage=Math.ceil(count[0].count/10)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +'    SELECT ROW_NUMBER() OVER (ORDER BY Message_ID desc) AS RowNumber,* FROM Message  where Message_Status=1'
                         if(req.query.name){
                             sqlstr+=" and Message_PersonName like '%"+req.query.name.replace(reg,'')+"%'"
                         }
                         if(req.query.content){
                             sqlstr+=" and Message_Content like '%"+req.query.content.replace(reg,'')+"%'"
                         }
                         sqlstr+=') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,results){
                         callback(err,results)
                     })

                 });


             }

         ], function (err,results) {
             console.log(page)
             res.render('message', {
                 title: '留言板',
                 results:results,
                 page:page,
                 menu: "menu10"
             });
         });

     });
     app.post('/addMessage',function(req,res){
         var name=req.body.name;
         var content=req.body.content;
         var phone=req.body.phone;
         var email=req.body.email;
         var now=dateFormat(new Date(),'yyyy/mm/dd hh:MM:ss');
         var sqlstr='INSERT INTO [Message]'
         +' (Message_Status, Message_PersonName, Message_EMail, Message_Phone, Message_Content, Message_Answer, Message_MessageDateTime, Message_AnswerDateTime, Message_State)'
         +" VALUES ('1', '"+name+"', '"+email+"', '"+phone+"', '"+content+"', '', '"+now+"', '', '1')"
         sqlConn(sqlstr,function(err,results){
             res.redirect('/message');

         })
     })
     app.get('/messageDetails', function (req, res) {
         async.waterfall([


             function(callback){
                 var sqlstr='select * from Message where Message_ID='+req.query.id;
                 sqlConn(sqlstr,function(err,data){
                     callback(err,data)
                 })
             }
         ], function (err,data) {
             res.render('messageDetails', {
                 title: '留言板',
                 data:data,
                 menu: "menu10"
             });
         });

     });
     app.get('/personStyle', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select count(1) count from PersonPre where PersonPre_Status=1'

                 sqlConn(sqlstr,function(err,count){
                    console.log(count)
                     page.totalPage=Math.ceil(count[0].count/10)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +'    SELECT ROW_NUMBER() OVER (ORDER BY PersonPre_ID desc) AS RowNumber,* from PersonPre where PersonPre_Status=1'
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     console.log(sqlstr)
                     sqlConn(sqlstr,function(err,results){

                         console.log(results)
                         callback(err,results)
                     })

                 });


             }

         ], function (err,results) {
             res.render('personStyle', {
                 title: '人才风采',
                 results:results,
                 page:page,
                 menu: "menu9"
             });
         });

     });
     app.get('/searchCourseware', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var reg=/[ ]/g;

                 var sqlstr='select count(1) count from Courseware cw left join CoursewareSort cs on cw.Courseware_SortID=cs.CoursewareSort_ID where cw.Courseware_Status=1 and cs.CoursewareSort_Status=1'
                 sqlstr+=" and Courseware_Name like '%"+req.query.keyword.replace(reg,'')+"%'"

                 sqlConn(sqlstr,function(err,count){

                     page.totalPage=Math.ceil(count[0].count/10)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +'    SELECT ROW_NUMBER() OVER (ORDER BY Courseware_ID desc) AS RowNumber,* FROM Courseware left join CoursewareSort on Courseware_SortID=CoursewareSort_ID where Courseware_Status=1 and CoursewareSort_Status=1'
                         +" and Courseware_Name like '%"+req.query.keyword.replace(reg,'')+"%'"
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,results){
                         callback(err,results)
                     })

                 });


             }
         ], function (err,results) {
             res.render('searchCourseware', {
                 title: '课件超市',
                 results:results,
                 page:page,
                 menu: "menu7"
             });
         });

     });
     app.get('/showContent1', function (req, res) {
         res.render('showContent', {
             title: '详情',
             id:req.query.id,
             menu: "menu1"
         });

     });
     app.get('/showContent', function (req, res) {
         var sqlstr='select * from  FileContent where FileContent_ID='+req.query.id;
         sqlConn(sqlstr,function(err,result){
             if(result[0]){
                 if(result[0].FileContent_Type.trim()=='multipart/related'){
                     result[0].FileContent_Type='message/rfc822'
                 }
                 res.setHeader('Content-Type',result[0].FileContent_Type)
                 res.setHeader("Content-Disposition", "inline;");
                 //res.setHeader('content-type', 'text/html;charset=gb2312');
                 var b = new Buffer(result[0].FileContent_Content, 'base64')//2进制流--数组
                 //var s= b.toString();
                 //console.log(b.length)
                 res.end(b);
             }else{
                 res.render('showContent1', {
                    data:'没有找到相关内容'
                 });
             }
//             res.render('showContent1', {
//                 s:s
//             });
         });


     });
     app.get('/moreSupply', function (req, res) {
         var page={
             curPage:req.query.page?req.query.page:1
         };
         async.waterfall([

             function(callback){
                 //var sqlstr='SELECT * from  SupplyDemand  sd where sd.SupplyDemand_Status=1 order by sd.SupplyDemand_ID desc'
                 var sqlstr='select count(1) count from SupplyDemand  where SupplyDemand_Status=1'

                 sqlConn(sqlstr,function(err,count){

                     page.totalPage=Math.ceil(count[0].count/10)
                     console.log(page)
                     sqlstr='SELECT TOP 10 * '
                         +'FROM'
                         +'('
                         +'    SELECT ROW_NUMBER() OVER (ORDER BY SupplyDemand_ID desc) AS RowNumber,* FROM SupplyDemand  where SupplyDemand_Status=1 '
                         +') A'
                         +' WHERE RowNumber>10*'+(page.curPage-1)
                     sqlConn(sqlstr,function(err,results){
                         callback(err,results)
                     })

                 });


             }
         ], function (err,results) {
             console.log(results)
             res.render('moreSupply', {
                 title: '供求信息',
                 results:results,
                 page:page,
                 menu: "menu1"
             });
         });

     });
     app.get('/test', function (req, res) {
         req.session.user=null;

         res.render('test', { title: '首页', menu: ''});
     });

     app.get('/reg', function (req, res) {
         var sqlstr;
         async.waterfall([
             function(callback){
                 sqlstr="SELECT * FROM [Department] WHERE Department_Status = '1' AND Department_ParentID = '0' AND  1 = '1'"
                 sqlConn(sqlstr,function(err,level_one){
                    callback(err,level_one)
                 })
             },
             function(level_one,callback){
                 sqlstr="SELECT * FROM [Department] WHERE Department_Status = '1' AND Department_ParentID = '"+level_one[0].Department_ID+"' AND  1 = '1' "
                 sqlConn(sqlstr,function(err,level_two){
                     var count = 0;

                     async.whilst(
                         function () { return count < level_two.length; },
                         function (callback) {

                             sqlstr="SELECT * FROM [Department] WHERE Department_Status = '1' AND Department_ParentID = '"+level_two[count].Department_ID+"' AND  1 = '1'"
                             sqlConn(sqlstr,function(err,level_three){
                                 level_two[count].level_three=level_three;
                                 count++;
                                 callback();
                             })
                         },
                         function (err) {
                             callback(err,level_one,level_two);
                         }
                     );
                 })
             }

         ], function (err,level_one,level_two) {
             console.log(level_one)
             res.render('reg', {
                 title: '注册',
                 one:level_one,
                 two:level_two,
                 menu: 'menu1'
             });
         });


     });
     //app.get('/reg', checkNotLogin);
     app.post('/reg', function (req, res) {
         var username=req.body.username;
         var password=req.body.password;
         var departmentId=req.body.departmentId;
         var jobNumber=req.body.jobNumber;
         var personName=req.body.personName;
         var idCard=req.body.idCard;
         var sqlstr="select * from Users where Users_Username='"+username+"' and Users_Status=1"

        sqlConn(sqlstr,function(err,datas){
            if(datas.length>0){
                req.flash('error','用户名已存在！！')
                return res.redirect('back')
            }else{
                sqlstr="INSERT INTO [Users] (Users_Status, Users_Username, Users_Password, Users_UserGroupID, Users_DepartmentID, Users_PersonID, Users_JobNumber, Users_PersonName, Users_IDCard, Users_Register) VALUES ('1', '"+username+"', '"+password+"', '5', '"+departmentId+"', '"+jobNumber+"', '"+jobNumber+"', '"+personName+"', '"+idCard+"', '1')";
                sqlConn(sqlstr,function(err,results){
                    req.flash('success','注册成功,请登陆！！！')
                    return res.redirect('/')
                })
            }
        })

     });
     app.post('/login', function (req, res) {
         var name = req.body.name;
         var password = req.body.password;
         var sqlstr="select * from Users where Users_Status=1 and Users_Username='"+name+"'"
         sqlConn(sqlstr,function(err,user){
             if(user.length==0){
                 req.flash('error','用户名不存在！！！')


             }else if(password!=user[0].Users_Password.trim()){
                 req.flash('error','密码错误，请重新登录！！！')

             }else{
                 req.flash('success','登录成功')
                 req.session.user=user[0];
             }
             return res.redirect('back');
         })
//         user.findUserByName(username, function (err, doc) {
//             if (!doc) {
//                 req.flash('error', res.__('warn').n_user);
//                 return res.redirect('/');
//             }
//             if (doc.password != password) {
//                 req.flash('error', res.__('warn').e_pwd);
//                 return res.redirect('/');
//             }
//             req.session.user =doc;
//             req.flash('success', res.__('warn').s_login);
//             res.redirect('/findPartner');
//
//
//         })

     });
     app.get('/player',function(req,res){
         res.render('player',{
             title: '视频播放',
             addr:req.query.addr,
             menu: 'menu1'
         })
     })
     app.get('/player1',function(req,res){
         var sqlstr="select * from TrainingPerson where TrainingPerson_TrainingID='"+req.query.tid+"' and TrainingPerson_UserID='"+req.session.user.Users_ID+"'"
         console.log(sqlstr)
         sqlConn(sqlstr,function(err,results){
             if(results.length==0){
                 req.flash('error','你没有在该培训班中！！！')
                 return res.redirect('back')
             }else{
                 Array.prototype.in_array = function(e)
                 {
                     for(i=0;i<this.length;i++)
                     {
                         if(this[i] == e)
                             return true;
                     }
                     return false;
                 }
                 if(['mp4','flv'].in_array(req.query.addr.split('.')[1].trim())){
                     res.render('player',{
                         title: '视频播放',
                         addr:req.query.addr,
                         menu: 'menu1'
                     })
                 }else{
                     res.redirect(req.session.url+req.query.addr)
                 }

             }
         })

     })
     app.get('/logout', function (req, res) {
         req.session.user = null;
         req.flash('success','退出成功');
         res.redirect('/');
     });

 };

